/* =========================================================
EMAIL BINDING — additive only. Does not modify doPost's
existing branches or upsertIntern_. Paste these functions
anywhere in Code.gs, then add the two small hooks noted
below into doPost/doGet.
========================================================= */
// Ensures the Interns sheet has an "email" column, adding it once
// if missing. Safe to call on every request — it's a no-op after
// the first time. Returns the 0-indexed column position.
function ensureEmailColumn_(sh) {
var headers = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
var emailCol = headers.indexOf('email');
if (emailCol === -1) {
sh.getRange(1, headers.length + 1).setValue('email');
emailCol = headers.length;
}
return emailCol;
}
// Links a verified email to an existing intern_id row in Interns.
// Returns true if a matching row was found and updated, false if
// the intern_id doesn't exist yet (caller should upsertIntern_ first,
// same as doPost's normal submit flow already does).
function linkEmailToIntern_(ss, internId, email) {
var sh = ensureSheet_(ss, "Interns", [
"first_seen_ts", "last_seen_ts", "intern_id", "user_id", "anon_id", "track", "status"
]);
var emailColIdx = ensureEmailColumn_(sh);
var data = sh.getDataRange().getValues();
var headers = data[0];
var idCol = headers.indexOf('intern_id');
for (var i = 1; i < data.length; i++) {
if (String(data[i][idCol]) === String(internId)) {
sh.getRange(i + 1, emailColIdx + 1).setValue(email);
return true;
}
}
return false;
}
// Looks up the intern_id already linked to a given email, if any.
// Used for cross-device recovery — a new browser with no local
// intern_id yet can ask "do you already know me by this email?"
// before generating a fresh random one.
function findInternIdByEmail_(ss, email) {
var sh = ss.getSheetByName("Interns");
if (!sh) return null;
var data = sh.getDataRange().getValues();
if (data.length < 2) return null;
var headers = data[0];
var idCol = headers.indexOf('intern_id');
var emailCol = headers.indexOf('email');
if (emailCol === -1) return null;
var target = String(email).toLowerCase();
for (var i = 1; i < data.length; i++) {
if (String(data[i][emailCol]).toLowerCase() === target) {
return String(data[i][idCol]);
}
}
return null;
}
/* =========================================================
HOOK 1 — inside doPost(e), right after the existing line:
if(!internId) return jsonOut({ok:false, error:"missing_intern_id"});
add this new action branch BEFORE the rest of doPost's logic
(submit / week logging) runs, so link_email short-circuits
cleanly without touching the existing submit path at all:
if (action === 'link_email') {
var email = String(payload.email || '').toLowerCase().trim();
if (!email || email.indexOf('@') < 0) {
return jsonOut({ok:false, error:'invalid_email'});
}
var linked = linkEmailToIntern_(ss, internId, email);
return jsonOut({ok:true, linked:linked, intern_id:internId, email:email});
}
========================================================= */
/* =========================================================
HOOK 2 — this project currently has NO doGet at all (only
doPost), which means the client can submit but never ask
the server anything. Add this whole function as new:
========================================================= */
function doGet(e) {
try {
var action = String((e && e.parameter && e.parameter.action) || '').toLowerCase();
var ss = SpreadsheetApp.getActiveSpreadsheet();
if (action === 'lookup_by_email') {
var email = String((e.parameter && e.parameter.email) || '').toLowerCase().trim();
if (!email || email.indexOf('@') < 0) {
return jsonOut({ ok: false, error: 'invalid_email' });
}
var internId = findInternIdByEmail_(ss, email);
if (!internId) return jsonOut({ ok: false, error: 'not_found' });
return jsonOut({ ok: true, intern_id: internId, email: email });
}
return jsonOut({ ok: false, error: 'unknown_action' });
} catch (e2) {
return jsonOut({ ok: false, error: 'server_error', detail: String(e2 && e2.message ? e2.message : e2) });
}
}